##################
## ORACLE DB
##################

-- 创建用户表
create table sys_user(
       id number(18) primary key,
       username varchar2(50),
       name varchar2(50),
       password varchar2(50),
       salt varchar2(50),
       state varchar2(1)
);
-- 创建用户表序列
create sequence seq_sys_user
start with 1
increment by 1
maxvalue 999999999999999
nocycle
cache 30

-- 创建用户表
create table user_info(
       id number(18) primary key,
       username varchar2(50),
       name varchar2(50),
       password varchar2(50),
       salt varchar2(50),
       state varchar2(1)
);
-- 创建用户表序列
create sequence seq_user_info
start with 1
increment by 1
maxvalue 999999999999999
nocycle
cache 30


-- 创建角色表
create table sys_role(
       id number(18) primary key,
       role varchar(50),
       description varchar(300),
       available varchar(1)
);
-- 创建角色表序列
create sequence seq_sys_role
start with 1
increment by 1
maxvalue 999999999999999
nocycle
cache 30

-- 创建权限表
-- drop table sys_permission;
create table sys_permission(
       id number(18) primary key,
       name varchar(50),
       resource_type varchar(20),
       url varchar(150),
       permission varchar(50),
       parent_id number(18),
       parent_ids varchar(100)，
       AVAILABLE varchar(50)
);

-- 创建权限表序列
create sequence seq_sys_permission
start with 1
increment by 1
maxvalue 999999999999999
nocycle
cache 30

-- 创建角色和权限的中间表
create table sys_role_permission(
       id number(18) primary key,
       role_id number(18),
       permission_id number(18)
);

-- 创建角色权限中间表序列
create sequence seq_sys_role_permission
start with 1
increment by 1
maxvalue 999999999999999
nocycle
cache 30

-- 创建用户和角色的中间表
create table sys_user_role(
       id number(18) primary key,
       role_id number(18),
       user_id number(18)
);
-- 创建用户角色中间表序列
create sequence seq_sys_user_role
start with 1
increment by 1
maxvalue 999999999999999
nocycle
cache 30

-- mock data
-- sys_permission
-- INSERT INTO Sys_Permission VALUES (seq_sys_permission.nextval, '用户管理', 'menu', 'userInfo/userList', 'userInfo:view','0','0/', 'Y');
-- INSERT INTO Sys_Permission VALUES (seq_sys_permission.nextval, '用户添加', 'button',  'userInfo/userAdd', 'userInfo:add','1', '0/1', 'Y');
select t.*,rowid from sys_permission t;
-- sys_role
-- INSERT INTO Sys_Role VALUES (seq_sys_role.nextval, '管理员', 'admin','Y');
-- INSERT INTO Sys_Role VALUES (seq_sys_role.nextval, 'VIP会员', 'vip','N');

select t.*,rowid from sys_role t;

-- sys_user_info
-- INSERT INTO User_Info VALUES (seq_user_info.nextval, 'admin', '管理员', 'd3c59d25033dbf980d29554025c23a75', '8d78869f470951332959580424d4bf4f', '0');
select t.*,rowid from user_info t;

-- sys_user_role

select t.*,rowid from sys_user_role t;
-- INSERT INTO Sys_Role_Permission VALUES (seq_sys_role_permission.nextval,'2', '2');
-- INSERT INTO Sys_Role_Permission VALUES (seq_sys_role_permission.nextval,'2', '3');
-- sys_role_permission

select t.*,rowid from sys_role_permission t;
-- INSERT INTO Sys_User_Role VALUES (seq_sys_user_role.nextval,'2', '2');
-- INSERT INTO Sys_User_Role VALUES (seq_sys_user_role.nextval,'2', '3');